﻿//创建时间: 2010-10-11
//创建作者: 李 城
//功能说明: 站点管理
//最后修改: 2010-10-11
//最后修改: 2010-11-24 李晓忠 添加 批量更新数据库里的URL
using System;
using System.Collections.Generic;
using System.Text;

using System.Data.Common;
using System.Data;
using TRPOP.Common;
using TRPOP.Config;
using TRPOP.CMS.Model;
using TRPOP.CMS.DALFactory;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

namespace TRPOP.CMS.DALSQLServer
{
    public class PSNDAL : IPSNDAL
    {

        #region 保存 Save
        public EnumSubmitResult Save(PSNInfo info)
        {
            string strStoreProcedure = SystemInfoHelper.getSystemInfo().DBprefixTableName + "_" + "PSN_Save";
            SqlDatabase odbDataBase = new SqlDatabase(SystemInfoHelper.getSystemInfo().CMSDataBaseConnection);
            DbCommand odbCommand = odbDataBase.GetStoredProcCommand(strStoreProcedure);

            odbDataBase.AddInParameter(odbCommand, "@Name", System.Data.DbType.String, info.Name);
            odbDataBase.AddInParameter(odbCommand, "@Type", System.Data.DbType.Int32, info.Type);
            odbDataBase.AddInParameter(odbCommand, "@Url", System.Data.DbType.String, info.Url);
            odbDataBase.AddInParameter(odbCommand, "@Root", System.Data.DbType.String, info.Root);
            odbDataBase.AddInParameter(odbCommand, "@Description", System.Data.DbType.String, info.Description);
            odbDataBase.AddInParameter(odbCommand, "@AddTime", System.Data.DbType.DateTime, info.AddTime);
            odbDataBase.AddInParameter(odbCommand, "@Xml", System.Data.DbType.String, info.Xml);
            odbDataBase.AddParameter(odbCommand, "@psnid", System.Data.DbType.Int32, ParameterDirection.InputOutput, null, DataRowVersion.Default, info.psnid);

            EnumSubmitResult enmResult = EnumSubmitResult.Failed;
            try
            {
                odbDataBase.ExecuteNonQuery(odbCommand);
                info.psnid = (int)odbDataBase.GetParameterValue(odbCommand, "@psnid");
                enmResult = EnumSubmitResult.Success;
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return enmResult;
        }

        #endregion

        #region 获取实体 GetInfoById
        public PSNInfo GetInfoById(int id)
        {
            string strSqlString = "SELECT * FROM " + SystemInfoHelper.getSystemInfo().DBprefixTableName + "_sys_PSN WHERE psnid=" + id;
            SqlDatabase odbDataBase = new SqlDatabase(SystemInfoHelper.getSystemInfo().CMSDataBaseConnection);
            DbCommand odbCommand = odbDataBase.GetSqlStringCommand(strSqlString);
            PSNInfo oInfo = null;
            try
            {
                using (IDataReader reader = odbDataBase.ExecuteReader(odbCommand))
                {
                    if (reader.Read())
                    {
                        oInfo = new PSNInfo();
                        ModelHelper.LoadInfoData(oInfo, reader);
                    }
                }
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return oInfo;
        }
        #endregion

        #region 删除 DeleteInfoById
        public EnumSubmitResult DeleteInfoById(int id)
        {
            return DeleteInfoByIds(id.ToString());
        }
        #endregion

        #region 删除 DeleteInfoByIds
        public EnumSubmitResult DeleteInfoByIds(string ids)
        {
            string strSqlString = "DELETE FROM " + SystemInfoHelper.getSystemInfo().DBprefixTableName + "_sys_PSN WHERE psnid in(" + ids + ")";
            SqlDatabase odbDataBase = new SqlDatabase(SystemInfoHelper.getSystemInfo().CMSDataBaseConnection);
            DbCommand odbCommand = odbDataBase.GetSqlStringCommand(strSqlString);

            EnumSubmitResult enmResult = EnumSubmitResult.Failed;
            try
            {
                odbDataBase.ExecuteNonQuery(odbCommand);
                enmResult = EnumSubmitResult.Success;
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return enmResult;
        }
        #endregion

        #region 自定义列表（无分页） GetListByCondition
        public List<PSNInfo> GetListByCondition(int topn, string condition, string orderby)
        {
            string strSqlString = "SELECT ";
            if (topn > 0)
            {
                strSqlString += " TOP " + topn;
            }
            strSqlString += " * FROM " + SystemInfoHelper.getSystemInfo().DBprefixTableName + "_sys_PSN WHERE 1=1"; if (condition != "")
            {
                strSqlString += condition;
            }
            if (orderby != "")
            {
                strSqlString += " ORDER BY " + orderby;
            }
            else
            {
                strSqlString += " ORDER BY psnid DESC";
            }

            SqlDatabase odbDataBase = new SqlDatabase(SystemInfoHelper.getSystemInfo().CMSDataBaseConnection);
            DbCommand odbCommand = odbDataBase.GetSqlStringCommand(strSqlString);
            List<PSNInfo> olstInfo = new List<PSNInfo>(); PSNInfo oInfo = null;
            try
            {
                using (IDataReader reader = odbDataBase.ExecuteReader(odbCommand))
                {
                    while (reader.Read())
                    {
                        oInfo = new PSNInfo();
                        ModelHelper.LoadInfoData(oInfo, reader);
                        olstInfo.Add(oInfo);
                    }
                }
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return olstInfo;
        }
        #endregion

        #region 自定义列表（分页） GetListByPager
        public List<PSNInfo> GetListByPager(int pageNumber, int pageSize, string condition, string orderby, out int recordCount)
        {
            string strStoreProcedure = SystemInfoHelper.getSystemInfo().PagerStoreProcedureName;
            SqlDatabase odbDataBase = new SqlDatabase(SystemInfoHelper.getSystemInfo().CMSDataBaseConnection);
            DbCommand odbCommand = odbDataBase.GetStoredProcCommand(strStoreProcedure);
            List<PSNInfo> olstInfo = new List<PSNInfo>(); PSNInfo oInfo = null;
            odbDataBase.AddInParameter(odbCommand, "@TableName", System.Data.DbType.String, SystemInfoHelper.getSystemInfo().DBprefixTableName + "_sys_PSN");
            odbDataBase.AddInParameter(odbCommand, "@ShowColumns", System.Data.DbType.String, "*");
            odbDataBase.AddInParameter(odbCommand, "@pageNumber", System.Data.DbType.Int32, pageNumber);
            odbDataBase.AddInParameter(odbCommand, "@pageSize", System.Data.DbType.Int32, pageSize);
            odbDataBase.AddInParameter(odbCommand, "@condition", System.Data.DbType.String, condition);
            odbDataBase.AddInParameter(odbCommand, "@orderby", System.Data.DbType.String, orderby);
            odbDataBase.AddOutParameter(odbCommand, "@recordCount", System.Data.DbType.Int32, 0);
            try
            {
                using (IDataReader reader = odbDataBase.ExecuteReader(odbCommand))
                {
                    while (reader.Read())
                    {
                        oInfo = new PSNInfo();
                        ModelHelper.LoadInfoData(oInfo, reader);
                        olstInfo.Add(oInfo);
                    }
                }
                recordCount = int.Parse(odbCommand.Parameters["@RecordCount"].Value.ToString());
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }
            return olstInfo;
        }
        #endregion

        #region 批量更新数据库里的URL
        /// <summary>
        /// 批量更新数据库里的URL
        /// </summary>
        /// <param name="oOldPSNInfo">旧PSN</param>
        /// <param name="oNewPSNInfo">新PSN</param>
        public EnumSubmitResult UpdateUrl(PSNInfo oOldPSNInfo, PSNInfo oNewPSNInfo)
        {          
            string strSQL = string.Empty;
            //strSQL =      " declare @old nvarchar(300);"+System.Environment.NewLine
            //            + " declare @new nvarchar(300);" + System.Environment.NewLine
            //            + " set @old='" + oOldPSNInfo.Url + "';" + System.Environment.NewLine
            //            + " set @new='" + oNewPSNInfo.Url + "';" + System.Environment.NewLine
            //            + " declare @NodeId table(id int);" + System.Environment.NewLine
            //            //--获取相关结点ID
            //            + " INSERT INTO @NodeId SELECT NodeId	FROM " + strprefixDB + "_node_Node"
            //            + " WHERE ContentPubTypeId=1 AND HtmlUrl like'{PSN-URL:" + oNewPSNInfo.psnid + "}%';  " + System.Environment.NewLine
            //            //--更新结点URL
            //            + " UPDATE " + strprefixDB + "_node_Node"
            //            +" SET URL=replace(URL,@old,@new)"
            //            + " WHERE NodeId in (SELECT ID FROM @NodeId);  " + System.Environment.NewLine
            //             //--更新基表URL
            //            + " UPDATE " + strprefixDB + "_content_Content"
            //            +" SET URL=replace(URL,@old,@new)"
            //            + " WHERE  NodeId in (SELECT ID FROM @NodeId) AND SelfURL=''; " + System.Environment.NewLine
            //            //--更新自定义发布URL
            //            + " UPDATE " + strprefixDB + "_content_Content"
            //            +" SET URL=replace(URL,@old,@new)"
            //            + " WHERE  SelfURL like'{PSN-URL:" + oNewPSNInfo.psnid + "}%'; " + System.Environment.NewLine
            //            //--更新附加发布URL
            //            + " UPDATE  " + strprefixDB + "_node_NodeAddionalPub"
            //            +" SET URL=replace(URL,@old,@new)"
            //            + " WHERE NodeId in (SELECT ID FROM @NodeId) AND SelfPSNURL=''; " + System.Environment.NewLine
            //            //--更新自定义附加发布URL
            //            + " UPDATE  " + strprefixDB + "_node_NodeAddionalPub"
            //            +" SET URL=replace(URL,@old,@new)"
            //            + " WHERE  SelfPSNURL like'{PSN-URL:" + oNewPSNInfo.psnid + "}%'; " + System.Environment.NewLine;

            strSQL = @"
                         declare @old nvarchar(300);
                         declare @new nvarchar(300);
                         set @old='{0}';
                         set @new='{1}';
                         declare @NodeId table(id int);

                         --HTML_URL
                         INSERT INTO @NodeId SELECT NodeId	FROM {3}_node_Node WHERE ContentPubTypeId=1 AND HtmlUrl like'{2}%';  
                         --select * from @NodeId
                         UPDATE {3}_node_Node SET URL=replace(URL,@old,@new) WHERE NodeId in (SELECT ID FROM @NodeId);  

                         --更新内容
                         UPDATE {3}_content_Content SET URL=replace(URL,@old,@new) WHERE  NodeId in (SELECT ID FROM @NodeId) AND SelfURL=''; 
                         UPDATE {3}_content_Content SET URL=replace(URL,@old,@new) WHERE  SelfURL like'{2}%'; 
                         
                        --附加发布
                         UPDATE  {3}_node_NodeAddionalPub SET URL=replace(URL,@old,@new) WHERE NodeId in (SELECT ID FROM @NodeId) AND SelfPSNURL=''; 
                         UPDATE  {3}_node_NodeAddionalPub SET URL=replace(URL,@old,@new) WHERE  SelfPSNURL like'{2}%';
                        
                       ";
            strSQL = string.Format(strSQL, oOldPSNInfo.Url, oNewPSNInfo.Url, "{PSN-URL:" + oNewPSNInfo.psnid + "}", SystemInfoHelper.getSystemInfo().DBprefixTableName);

            SqlDatabase odbDataBase = new SqlDatabase(SystemInfoHelper.getSystemInfo().CMSDataBaseConnection);
            DbCommand odbCommand = odbDataBase.GetSqlStringCommand(strSQL);

            EnumSubmitResult enmResult = EnumSubmitResult.Failed;
            try
            {
                odbDataBase.ExecuteNonQuery(odbCommand);
                enmResult = EnumSubmitResult.Success;
            }
            catch (Exception oeException)
            {
                throw new Exception(oeException.Message, oeException);
            }

            return enmResult;

        }
        #endregion
    }
}
